NAME¶

pg_upgrade - upgrade a PostgreSQL server instance

SYNOPSIS¶

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...]

DESCRIPTION¶

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.

Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g. by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade supports upgrades from 8.3.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.

OPTIONS¶

pg_upgrade accepts the following command-line arguments:

-b old_bindir, --old-bindir=old_bindir

-B new_bindir, --new-bindir=new_bindir

-c, --check

-d old_datadir, --old-datadir=old_datadir

-D new_datadir, --new-datadir=new_datadir

-k, --link

-o options, --old-options options

-O options, --new-options options

-p old_port_number, --old-port=old_portnum

-P new_port_number, --new-port=new_portnum

-r, --retain

-u user_name, --user=user_name

-v, --verbose

-V, --version

-?, -h, --help

USAGE¶

These are the steps to perform an upgrade with pg_upgrade:

mv /usr/local/pgsql /usr/local/pgsql.old

gmake prefix=/usr/local/pgsql.new install

pg_ctl -D /opt/PostgreSQL/8.4 stop
pg_ctl -D /opt/PostgreSQL/9.0 stop

NET STOP postgresql-8.4
NET STOP postgresql-9.0

NET STOP pgsql-8.3  (PostgreSQL 8.3 and older used a different service name)

RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.0\bin;

pg_upgrade.exe


        --old-datadir "C:/Program Files/PostgreSQL/8.4/data"


        --new-datadir "C:/Program Files/PostgreSQL/9.0/data"


        --old-bindir "C:/Program Files/PostgreSQL/8.4/bin"


        --new-bindir "C:/Program Files/PostgreSQL/9.0/bin"

psql --username postgres --file script.sql postgres

NOTES¶

pg_upgrade does not support upgrading of databases containing these reg* OID-referencing system data types: regproc, regprocedure, regoper, regoperator, regconfig, and regdictionary. (regtype can be upgraded.)

All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically.

For deployment testing, create a schema-only copy of the old cluster, insert dummy data, and upgrade that.

If you are upgrading a pre-PostgreSQL 9.2 cluster that uses a configuration-file-only directory, you must pass the real data directory location to pg_upgrade, and pass the configuration directory location to the server, e.g. -d /real-data-directory -o '-D /configuration-directory'.

If using a pre-9.1 old server that is using a non-default Unix-domain socket directory or a default that differs from the default of the new cluster, set PGHOST to point to the old server's socket location. (This is not relevant on Windows.)

A Log-Shipping Standby Server (Section 25.2, “Log-Shipping Standby Servers”, in the documentation) cannot be upgraded because the server must allow writes. The simplest way is to upgrade the primary and use rsync to rebuild the standbys. You can run rsync while the primary is down, or as part of a base backup (Section 24.3.2, “Making a Base Backup”, in the documentation) which overwrites the old standby cluster.

If you want to use link mode and you do not want your old cluster to be modified when the new cluster is started, make a copy of the old cluster and upgrade that in link mode. To make a valid copy of the old cluster, use rsync to create a dirty copy of the old cluster while the server is running, then shut down the old server and run rsync again to update the copy with any changes to make it consistent. You might want to exclude some files, e.g. postmaster.pid, as documented in Section 24.3.3, “Making a Base Backup Using the Low Level API”, in the documentation.

SEE ALSO¶

initdb(1), pg_ctl(1), pg_dump(1), postgres(1)